package org.nebuloop.photo.organizer.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.apache.commons.lang.StringUtils;

public class DBSetup {
  public static enum DATABASE_TYPE {
    HSQLDB, DERBY, MYSQL
  }

  private static String JDBC_DRIVER;

  private static DATABASE_TYPE DB_TYPE = DATABASE_TYPE.MYSQL;
  private static final Logger LOGGER = Logger.getLogger(DBSetup.class.getName());
  private static final AtomicBoolean isDatabaseInitialized = new AtomicBoolean(false);

  public static void init(String JDBC_DRIVER) {
    DBSetup.JDBC_DRIVER = JDBC_DRIVER;

    if (JDBC_DRIVER.indexOf("hsqldb") > 0) {
      DB_TYPE = DATABASE_TYPE.HSQLDB;
    } else if (JDBC_DRIVER.indexOf("derby") > 0) {
      DB_TYPE = DATABASE_TYPE.DERBY;
    } else if (JDBC_DRIVER.indexOf("mysql") > 0) {
      DB_TYPE = DATABASE_TYPE.MYSQL;
    }
    
    LOGGER.info("DB TYPE : " + DB_TYPE);
  }

  public synchronized static void setupDB(Connection connection) {
    if (StringUtils.isBlank(JDBC_DRIVER)) {
      throw new RuntimeException(DBSetup.class.getName() + ".init() is not called");
    }
    if (isDatabaseInitialized.getAndSet(true)) {
      LOGGER.info("Database already initialized...exiting");
      return;
    }
    LOGGER.info("Initializing Database");
    Statement stmt = null;
    try {
      stmt = connection.createStatement();
      String[] strDdls;
      switch (DB_TYPE) {
      case HSQLDB: {
        strDdls = new String[] {
            "create table IMAGE_INFO (ID IDENTITY, NAME VARCHAR(255) NOT NULL, DEST_PATH VARCHAR(255), "
                + "MD5 VARCHAR(50) NOT NULL, CREATE_TIME DATETIME, " + "IMPORTED_TIME DATETIME, HAS_EXIF BOOLEAN, HAS_GEOCODES BOOLEAN) " // +
                // "CONSTRAINT cnts1 UNIQUE (MD5)"
            ,
            "create table SRC_INFO (ID IDENTITY, IMG_ID INTEGER NOT NULL, SRC_PATH VARCHAR(255), SRC_ID VARCHAR(100), CHECKED_TIME DATETIME)" };
        break;
      }
      case DERBY: {
        strDdls = new String[] {
            "create table IMAGE_INFO (ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
                + "NAME VARCHAR(255) NOT NULL, DEST_PATH VARCHAR(255), "
                + "MD5 VARCHAR(50) NOT NULL, CREATE_TIME TIMESTAMP, " + "IMPORTED_TIME TIMESTAMP, HAS_EXIF SMALLINT, HAS_GEOCODES SMALLINT) " // +
                // "CONSTRAINT cnts1 UNIQUE (MD5)"
            ,
            "create table SRC_INFO (ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
                + "IMG_ID INTEGER NOT NULL, SRC_PATH VARCHAR(255), SRC_ID VARCHAR(100), CHECKED_TIME TIMESTAMP)" };
        break;
      }
      case MYSQL: {
        strDdls = new String[] {
            "CREATE TABLE IMAGE_INFO (ID INT  NOT NULL PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(255) NOT NULL, "
                + " DEST_PATH VARCHAR(500)  NOT NULL, MD5 VARCHAR(50)  NOT NULL, CREATE_TIME TIMESTAMP  NOT NULL, "
                + " IMPORTED_TIME TIMESTAMP  NOT NULL, HAS_EXIF BOOLEAN, HAS_GEOCODES BOOLEAN) ENGINE = MyISAM",
            "ALTER TABLE IMAGE_INFO ADD INDEX idx_md5(MD5)",
            "create table SRC_INFO (ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, IMG_ID INT NOT NULL,"
                + " SRC_PATH VARCHAR(255), SRC_ID VARCHAR(100), CHECKED_TIME TIMESTAMP)",
            "ALTER TABLE SRC_INFO ADD INDEX idx_imgid(IMG_ID)",
            "select name, (select count(b.ID) from SRC_INFO b where b.IMG_ID = i.ID) as cnt, DEST_PATH, SRC_PATH, SRC_ID from IMAGE_INFO i join SRC_INFO s on i.ID = s.IMG_ID where (select count(b.ID) from SRC_INFO b where b.IMG_ID = i.ID) > 1" };
        break;
      }
      default: {
        strDdls = new String[0];
      }
      }
      for (String strDdl : strDdls) {
        runDdl(strDdl, stmt);
      }
    } catch (SQLException ex) {
      LOGGER.log(Level.WARNING, "Error while creating tables. Ignore if it is related to table already exist", ex);
    } finally {
      try {
        if (stmt != null) {
          stmt.close();
        }
      } catch (SQLException e) {
        LOGGER.log(Level.WARNING, "Error in closing statement", e);
      }
    }
  }

  private static synchronized boolean runDdl(String strDdl, Statement stmt) throws SQLException {
    LOGGER.log(Level.INFO, "Running DDL : {0}", strDdl);
    boolean a = stmt.execute(strDdl);
    LOGGER.log(Level.INFO, "DDL returned : {0}", a);
    return a;
  }
}
